package cn.hit.thread;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import javax.swing.JProgressBar;
import javax.swing.JTextArea;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import cn.hit.been.HitCodeSortBeen;
import cn.hit.been.HitCodeValueBeen;
import cn.hit.constant.HitConstants;
import cn.hit.jdbc.DBAccessSington;
import cn.hit.utils.HitUtils;

public class CreateSqlWithExcelThread extends Thread {
    private File mainExcel = null;
    private JTextArea logArea;
    private JProgressBar pbar;
    private String targetPath;
    private StringBuilder exportLog;
    private DBAccessSington dbAccess = DBAccessSington.getInstance();

    //滚屏的时候码类信息
    private String codeSortPrintText = null;
    //码类的个数
    private int codeSortCount;
    //码值的个数
    private int codeValueCount;
    //进度条的临时变量
    private int tmpPbarValue;
    public CreateSqlWithExcelThread(File mainExcel, JTextArea logArea, JProgressBar pbar,
            String targetPath) {
        this.mainExcel = mainExcel;
        this.logArea = logArea;
        this.pbar = pbar;
        this.targetPath = targetPath;
        this.exportLog = new StringBuilder();
    }

    @Override
    public void run() {
        super.run();
        //从Excel读取信息放到码值Been集合里面
        List<HitCodeSortBeen> codeSortBeens = readCodeSortExcel(mainExcel);
        //把从Excel读取的码值Been相关信息
        String sql = generateSqlScriptWithBeens(codeSortBeens);
        logArea.setText("解析码类的个数：" + codeSortCount + "\n解析码值的个数：" + codeValueCount);
        HitUtils.exportSqlFile(targetPath, HitConstants.EXPORT_FILE_NAME_CODE_SCRIPT, sql);
        HitUtils.exportLogFile(targetPath, HitConstants.EXPORT_FILE_NAME_CODE_LOG, exportLog.toString());
    }

    /**
     * 这是读取码类Excel的方法，返回码类的Been集合
     * 
     * @param excel
     * @return
     * @throws Exception
     */
    private List<HitCodeSortBeen> readCodeSortExcel(File excel){
        List<HitCodeSortBeen> codeSortBeens = new ArrayList<HitCodeSortBeen>();
        InputStream ins;
        try {
            ins = new FileInputStream(excel);
            Workbook workBook = WorkbookFactory.create(ins);
            Sheet sheet = workBook.getSheetAt(workBook.getActiveSheetIndex());
            Row row = null;
            Cell cell = null;
            HitCodeSortBeen codeSortBeen = null;
            // 得到最大Y坐标
            int coordinateY = sheet.getPhysicalNumberOfRows();
            String[] codeSortFields = HitConstants.DB_CODE_SORT_FIELDS;
            //Log标记
            String markLog = "{#markLog#}";
            for (int y = 0; y < coordinateY; y++) {
                row = sheet.getRow(y);
                Cell containUrlCell = row.getCell(HitConstants.EXCEL_URL_X_COORDINATE);
                Cell operationStatusCell = row.getCell(HitConstants.EXCEL_OPERATION_STATUS_X_COORDINATE);
                String strStatus = operationStatusCell.getStringCellValue();
                // 当Excel行不等于空且有码值(Cell的URL不等于空)
                if (row != null && containUrlCell.getHyperlink() != null &&
                        HitConstants.COMMON_OPERATION_STATUS_NAME.equals("'" + strStatus + "'")) {
                    //计算码类的个数
                    codeSortCount ++;
                    // 得到最大X坐标
                    int coordinateX = codeSortFields.length;
                    codeSortBeen = new HitCodeSortBeen();
                    for (int x = HitConstants.EXCEL_START_READ_X_COORDINATE; x < coordinateX; x++) {
                        cell = row.getCell(x);
                        String methodName = "set" + codeSortFields[x].substring(0, 1).toUpperCase()
                                + codeSortFields[x].substring(1, codeSortFields[x].length());
                        try {
                            codeSortBeen.getClass().getMethod(methodName, String.class).invoke(
                                    codeSortBeen, HitUtils.getValueWithCell(cell));
                        } catch (Exception e) {
                            exportLog.append("\t\t码类Excel配置列名和数据库表列名不一致："+codeSortFields[x]+"******\n");
                            e.printStackTrace();
                        }
                        if (x == HitConstants.EXCEL_URL_X_COORDINATE && cell.getHyperlink() != null) {
                            String url = excel.getAbsolutePath().substring(0,
                                    excel.getAbsolutePath().lastIndexOf("\\"))
                                    + "\\" + cell.getHyperlink().getAddress();
                            codeSortBeen.setStrCodeValueFileUrl(url);
                            exportLog.append(markLog);
                            codeSortBeen.setCodeValueBeens(readCodeValueExcel(new File(url)));
                        }
                    }
                    String log = "码类" + codeSortBeen.getStrShowName() + "("
                            + codeSortBeen.getStrCodeSortNo() + ")下码值数量："
                            + codeSortBeen.getCodeValueBeens().size() + " ******读取成功\n";
                    int start = exportLog.indexOf(markLog);
                    int end = start + markLog.length();
                    exportLog.replace(start, end, log);
                    codeSortBeens.add(codeSortBeen);
                }
            }
        } catch (FileNotFoundException e1) {
            exportLog.append("\t\t码类Excel文件路径不正确："+excel.getAbsolutePath()+"******\n");
            e1.printStackTrace();
        } catch (InvalidFormatException e) {
            exportLog.append("\t\t码类Excel文件损坏："+excel.getAbsolutePath()+"******\n");
            e.printStackTrace();
        } catch (IOException e) {
            exportLog.append("\t\t读取码类Excel文件时出错："+excel.getAbsolutePath()+"******\n");
            e.printStackTrace();
        }
        return codeSortBeens;
    }

    /**
     * 这是读取码值Excel的方法，返回码值的Been集合
     * 
     * @param excel
     * @return
     * @throws Exception
     */
    private List<HitCodeValueBeen> readCodeValueExcel(File excel) {
        List<HitCodeValueBeen> codeValueBeens = new ArrayList<HitCodeValueBeen>();
        InputStream ins;
        try {
            ins = new FileInputStream(excel);
            Workbook workBook = WorkbookFactory.create(ins);
            Sheet sheet = workBook.getSheetAt(workBook.getActiveSheetIndex());
            Row row = null;
            Cell cell = null;
            HitCodeValueBeen codeCalueBeen = null;
            // 定义最大Y坐标
            int coordinateY = sheet.getPhysicalNumberOfRows();
            String[] codeValueFields = HitConstants.DB_CODE_VALUE_FIELDS;
            for (int y = 0; y < coordinateY; y++) {
                row = sheet.getRow(y);
                cell = row.getCell(0);
                String codeValueNo = HitUtils.getValueWithCell(cell);
                //码值编号不为空且里面不包含汉字的情况下是真正的码值
                if (row != null && !"".equals(codeValueNo) && !HitUtils.constainChinese(codeValueNo)) {
                    //计算码类的个数
                    codeValueCount ++;
                    // 定义最大X坐标
                    int coordinateX = codeValueFields.length;
                    codeCalueBeen = new HitCodeValueBeen();
                    for (int x = HitConstants.EXCEL_START_READ_X_COORDINATE; x < coordinateX; x++) {
                        cell = row.getCell(x);
                        String methodName = "set" + codeValueFields[x].substring(0, 1).toUpperCase()
                                + codeValueFields[x].substring(1, codeValueFields[x].length());
                        try {
                            codeCalueBeen.getClass().getMethod(methodName, String.class).invoke(
                                    codeCalueBeen, HitUtils.getValueWithCell(cell));
                        } catch (Exception e) {
                            exportLog.append("\t\t码值Excel配置列名和数据库表列名不一致：" + codeValueFields[x]+"******\n");
                            e.printStackTrace();
                        }
                    }
                    codeValueBeens.add(codeCalueBeen);
                    exportLog.append("码值" + codeCalueBeen.getStrShowName() + "("
                            + codeCalueBeen.getStrCodeValueNo() + ")******读取成功\n");
                }
            }
        } catch (FileNotFoundException e1) {
            exportLog.append("\t\t码值Excel文件路径不正确："+excel.getAbsolutePath()+"******\n");
            e1.printStackTrace();
        } catch (InvalidFormatException e) {
            exportLog.append("\t\t码值Excel文件损坏："+excel.getAbsolutePath()+"******\n");
            e.printStackTrace();
        } catch (IOException e) {
            exportLog.append("\t\t读取码值Excel文件时出错："+excel.getAbsolutePath()+"******\n");
            e.printStackTrace();
        }
        return codeValueBeens;
    }


    /**
     * 根据从Excel读取的信息拼接Sql语句
     * @param codeSortBeens
     * @return 生成Sql脚本
     * @throws Exception
     */
    private String generateSqlScriptWithBeens(List<HitCodeSortBeen> codeSortBeens){
        //设置进度条的最大值
        pbar.setMaximum(codeValueCount);
        //设置进度条可见
        pbar.setVisible(true);
        StringBuilder sql = new StringBuilder();
        for (HitCodeSortBeen hitCodeSortBeen : codeSortBeens) {
            // 首先设置码类相关联的信息
            try {
                dbAccess.setCodeSortBeenRelationInfo(hitCodeSortBeen);
            } catch (Exception e) {
                e.printStackTrace();
            }
            sql.append("\n-- 码类("+hitCodeSortBeen.getStrCodeSortNo()+" "+hitCodeSortBeen.getStrCodeSortName()+" ) "+hitCodeSortBeen.getDatModifyTime()+"\n");
            if(hitCodeSortBeen.isExist()){
                // 生成更新Sql语句
                sql.append(generateUpdateSqlWithCodeSortBeen(hitCodeSortBeen)+"\n");
            } else {
                // 生成新建Sql语句
                sql.append(generateInsertSqlWithCodeSortBeen(hitCodeSortBeen)+"\n");
            }
            //滚屏时要用到码类信息
            codeSortPrintText = "正在解析码类 "+hitCodeSortBeen.getStrShowName()+"("+hitCodeSortBeen.getStrCodeSortNo()+")";
            sql.append("-- 码值\n");
            //新建码值前必须把以前该码类下的码值删除
            sql.append("delete from " + HitConstants.DB_CODE_VALUE_TABLE_NAME + 
                      " where " + HitConstants.DB_CODE_SORT_FIELDS[0] + " = " + hitCodeSortBeen.getStrCodeSortId());
            //加上本码类下的码值脚本
            sql.append(generateSqlScriptWithCodeValueBeens(hitCodeSortBeen.getCodeValueBeens())+"\n");
        }
        //设置进度条不可见
        pbar.setVisible(false);
        return sql.toString();
    }

    /**
     * 根据从Excel读取的信息拼接Sql语句
     * @param codeSortBeens
     * @return 生成Sql脚本
     * @throws Exception
     */
    @SuppressWarnings("static-access")
    private String generateSqlScriptWithCodeValueBeens(List<HitCodeValueBeen> codeValueBeens){
        StringBuilder sql = new StringBuilder();
        for (HitCodeValueBeen hitCodeValueBeen : codeValueBeens) {
            // 首先设置码值相关联的信息
            try {
                dbAccess.setCodeValueBeenRelationInfo(hitCodeValueBeen);
            } catch (Exception e1) {
                e1.printStackTrace();
            }
            sql.append(generateInsertSqlWithCodeValueBeen(hitCodeValueBeen));
            //滚屏效果
            String printedText = logArea.getText();
            String[] tmpLog = printedText.split("\n");
            if (tmpLog.length > HitConstants.LOG_ROLL_LINE_SIZE) {
                printedText = logArea.getText()
                        .substring(logArea.getText().indexOf("......") + 7,
                                logArea.getText().length());
            } else {
                printedText = logArea.getText();
            }
            logArea.setText(printedText+codeSortPrintText+"下码值"+hitCodeValueBeen.getStrShowName()+"("+hitCodeValueBeen.getStrCodeValueNo()+")信息......\n");
            pbar.setValue(tmpPbarValue ++);
            try {
                this.sleep(HitConstants.PARSE_EXCEL_SLEEP_TIME);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
        return sql.toString();
    }

    /**
     * 拼接update码类的sql语句
     * @param hitCodeSortBeen
     * @return
     */
    private String generateUpdateSqlWithCodeSortBeen(HitCodeSortBeen hitCodeSortBeen){
        StringBuilder sql = new StringBuilder("update "+HitConstants.DB_CODE_SORT_TABLE_NAME+" set ");
        sql.append(" "+HitConstants.DB_CODE_SORT_FIELDS[2]+" = "+hitCodeSortBeen.getStrCodeSortCode());
        sql.append(" ,"+HitConstants.DB_CODE_SORT_FIELDS[3]+" = "+hitCodeSortBeen.getStrCodeSortName());
        sql.append(" ,"+HitConstants.DB_CODE_SORT_FIELDS[4]+" = "+hitCodeSortBeen.getStrShowName());
        sql.append(" ,"+HitConstants.DB_CODE_SORT_FIELDS[5]+" = "+hitCodeSortBeen.getStrCodeSortJm());
        sql.append(" ,"+HitConstants.DB_CODE_SORT_FIELDS[6]+" = "+hitCodeSortBeen.getStrCodeSortType());
        sql.append(" ,"+HitConstants.DB_CODE_SORT_FIELDS[7]+" = "+hitCodeSortBeen.getStrCodeSortSource());
        sql.append(" ,"+HitConstants.DB_CODE_SORT_FIELDS[8]+" = "+hitCodeSortBeen.getStrRightFlg());
        sql.append(" ,"+HitConstants.DB_CODE_SORT_FIELDS[9]+" = "+hitCodeSortBeen.getStrCodeSortStatus());
        sql.append(" ,"+HitConstants.DB_CODE_SORT_FIELDS[10]+" = "+hitCodeSortBeen.getStrValidFlg());
        sql.append(" ,"+HitConstants.DB_CODE_SORT_FIELDS[13]+" = "+hitCodeSortBeen.getDatModifyTime());
        sql.append(" ,"+HitConstants.DB_CODE_SORT_FIELDS[15]+" = "+hitCodeSortBeen.getStrComment());
        sql.append(" where strDelFlg = '0' and "+HitConstants.DB_CODE_SORT_FIELDS[0]+" = "+hitCodeSortBeen.getStrCodeSortId());
        return sql.toString();
    }

    /**
     * 拼接insert码类的sql语句
     * 
     * @param hitCodeSortBeen
     * @throws Exception
     * @return
     */
    private String generateInsertSqlWithCodeSortBeen(HitCodeSortBeen hitCodeSortBeen) {
        StringBuilder sql = new StringBuilder();
        sql.append("insert into " + HitConstants.DB_CODE_SORT_TABLE_NAME + "(");
        //为Sql设置字段名称
        for (int i = 0; i < HitConstants.DB_CODE_SORT_FIELDS.length; i++) {
            sql.append(HitConstants.DB_CODE_SORT_FIELDS[i]+",");
        }
        //设置strMOdifyId和datModifyTime字段
        sql.append(HitConstants.DB_COMMON_TABEL_FIELDS[0]+",");
        sql.append(HitConstants.DB_COMMON_TABEL_FIELDS[1]+")\nvalues(");
        //为Sql设置字段值
        for (int i = 0; i < HitConstants.DB_CODE_SORT_FIELDS.length; i++) {
            String filed = HitConstants.DB_CODE_SORT_FIELDS[i];
            String methodName = "get" + filed.substring(0, 1).toUpperCase()
                + filed.substring(1, filed.length());
            Object obj = null;
            try {
                obj = hitCodeSortBeen.getClass().getMethod(methodName).invoke(
                        hitCodeSortBeen);
            } catch (Exception e) {
                e.printStackTrace();
            } 
            sql.append(obj+",");
        }
        //设置strMOdifyId和datModifyTime值
        sql.append(hitCodeSortBeen.getStrCreateId()+",");
        sql.append("getDate())");
        return sql.toString();
    }

    /**
     * 拼接insert码值的sql语句
     * 
     * @param hitCodeSortBeen
     * @throws Exception
     * @return
     */
    private String generateInsertSqlWithCodeValueBeen(HitCodeValueBeen hitCodeValueBeen){
        StringBuilder sql = new StringBuilder();
        sql.append("\ninsert into " + HitConstants.DB_CODE_VALUE_TABLE_NAME + "(");
        //为Sql设置字段名称
        for (int i = 0; i < HitConstants.DB_CODE_VALUE_FIELDS.length; i++) {
            sql.append(HitConstants.DB_CODE_VALUE_FIELDS[i]+",");
        }
        //设置码值的所属码类的Id字段
        sql.append(HitConstants.DB_CODE_SORT_FIELDS[0]+",");
        //设置strMOdifyId和datModifyTime字段
        sql.append(HitConstants.DB_COMMON_TABEL_FIELDS[0]+",");
        sql.append(HitConstants.DB_COMMON_TABEL_FIELDS[1]+",");
        sql.append(HitConstants.DB_COMMON_TABEL_FIELDS[2]+",");
        sql.append(HitConstants.DB_COMMON_TABEL_FIELDS[3]+")\nvalues(");
        //为Sql设置字段值
        for (int i = 0; i < HitConstants.DB_CODE_VALUE_FIELDS.length; i++) {
            String filed = HitConstants.DB_CODE_VALUE_FIELDS[i];
            String methodName = "get" + filed.substring(0, 1).toUpperCase()
                + filed.substring(1, filed.length());
            Object obj = null;
            try {
                obj = hitCodeValueBeen.getClass().getMethod(methodName).invoke(
                        hitCodeValueBeen);
            }  catch (Exception e) {
                e.printStackTrace();
            }
            sql.append(obj+",");
        }
        //设置码值的所属码类的Id值
        sql.append(hitCodeValueBeen.getStrCodeSortId()+",");
        //设置strMOdifyId和datModifyTime值
        sql.append(hitCodeValueBeen.getStrCreateId()+",");
        sql.append("getDate(),");
        sql.append(hitCodeValueBeen.getStrCreateId()+",");
        sql.append(hitCodeValueBeen.getNumShowOrder()+")");
        return sql.toString();
    }
}
